# # 插入操作
    # db.session.execute("insert into hello_author(name) values('钱钟书')")
    # db.session.commit()
    #
    # # 更新操作
    # db.session.execute("update hello_author set name='abc' where name='bcd'")
    # db.session.commit()
    #
    # # 删除操作
    # db.session.exectut("delete from hello_author where name='abc'")
    # db.session.commit()
    #
    # # 查询操作
    # rest_one = db.session.execute("select * from hello_author").fetchone()  # 一条数据
    # rest_all = db.session.execute("select * from hello_author").fetchall()  # 所有数据
from sqlalchemy import text

from apps.ext import db

class dbCurdD(object):
    @staticmethod
    def dbListFiled(sql):
        # 获取字段
        try:
            rest = db.session.execute(sql)
        except Exception as e:
            return None,repr(e)
        # 游标对象的description属性得到由字段和其他值组成的元组，格式：((字段1，...),(字段2，...)...)
        # 通过列表推导式，遍历元组，取遍历后的元组的第一个索引的值组成一个有字段组成的列表
        field_li = [k[0].lower() for k in rest.cursor.description]
        return field_li,None
    @staticmethod
    def dbListJon(sql,parm=None):
        try:
            rest = db.session.execute(text(sql),parm)
        except Exception as e:
            return None,repr(e)
        # 游标对象的description属性得到由字段和其他值组成的元组，格式：((字段1，...),(字段2，...)...)
        # 通过列表推导式，遍历元组，取遍历后的元组的第一个索引的值组成一个有字段组成的列表
        field_li = [k[0].lower() for k in rest.cursor.description]
        # 所有数据
        try:
            data_all = rest.fetchall()  # 查询所有数据，fetchall()或fetchone()要放到description后面，否则会报错
        except Exception as e:
            return None,repr(e)
        result_list = list()
        for data in data_all:
            result = dict(zip(field_li, data))
            result_list.append(result)
        return result_list,None

    @staticmethod
    def dbOneJson(sql,parm=None):
        try:
            rest = db.session.execute(text(sql),parm)
        except Exception as e:
            return None,repr(e)
        # 游标对象的description属性得到由字段和其他值组成的元组，格式：((字段1，...),(字段2，...)...)
        # 通过列表推导式，遍历元组，取遍历后的元组的第一个索引的值组成一个有字段组成的列表
        field_li = [k[0].lower() for k in rest.cursor.description]
        # 所有数据
        try:
            data = rest.fetchone()
        except Exception as e:
            return None,repr(e)
        if data is None:
            return None, None
        result = dict(zip(field_li, data))
        return result,None

    @staticmethod
    def dbListSet(sql):
        try:
            rest = db.session.execute(sql)
        except Exception as e:
            return None,repr(e)
        # 游标对象的description属性得到由字段和其他值组成的元组，格式：((字段1，...),(字段2，...)...)
        # 通过列表推导式，遍历元组，取遍历后的元组的第一个索引的值组成一个有字段组成的列表
        field_li = [k[0] for k in rest.cursor.description]
        field_li = list(field_li)
        # 所有数据
        try:
            data_all = rest.fetchall()  # 查询所有数据，fetchall()或fetchone()要放到description后面，否则会报错
        except Exception as e:
            return None,repr(e)
        data = []
        if len(field_li) == 0:
            data = []
        if len(field_li) > 0:
            for i2 in range(len(field_li)):
                data.append(list(map(lambda x: x[i2], data_all)))
        return data, None

    @staticmethod
    def dbListLst(sql):
        try:
            rest = db.session.execute(sql)
        except Exception as e:
            return None, repr(e)
        # 游标对象的description属性得到由字段和其他值组成的元组，格式：((字段1，...),(字段2，...)...)
        # 所有数据
        try:
            data_all = rest.fetchall()  # 查询所有数据，fetchall()或fetchone()要放到description后面，否则会报错
        except Exception as e:
            return None, repr(e)
        data = []
        for itm in data_all:
            data.append(list(itm))
        return data, None

    @staticmethod
    def dbDelete(sql,parm=None):
        try:
            db.session.execute(text(sql),parm)
            db.session.commit()
        except Exception as e:
            return None,repr(e)
        return None,None
    @staticmethod
    def dbCreate(sql,parm=None):
        try:
            db.session.execute(text(sql),parm)
            db.session.commit()
        except Exception as e:
            return None,repr(e)
        return None,None

    @staticmethod
    def dbUpdate(sql,parm=None):
        try:
            db.session.execute(text(sql),parm)
            db.session.commit()
        except Exception as e:
            return None,repr(e)
        return None,None

    @staticmethod
    def dataInsertFormat(parameter):
        """
        字典：{'charset': 'utf8', 'db': '13123', 'engine': 'mysql', 'host': 12311, 'id': '20210825171151933828', 'name': '法第三方士大夫的'}
        返回结果：
        [('charset', 'db', 'engine', 'host', 'id', 'name')
        ('utf8', '13123', 'mysql', 12311, '20210825171151933828', '法第三方士大夫的')]
        :param parameter:
        :return:
        """
        key_str = ''
        value_list = []
        for key, value in parameter.items():
            key_str = key_str + key + ','
            value_str = value
            if value == True:
                value_str = 1
            if value == False:
                value_str = 0
            if value is None:
                value_str = ''
            # if type(value) == str:
            #     value_str = "'"+value+"'"
            value_list.append(value_str)
        return ['('+key_str[:-1]+')',tuple(value_list)]

    @staticmethod
    def dataUpdateFormat(parameter):
        """
        字典：{'charset': 'utf8', 'db': '13123', 'engine': 'mysql', 'host': '12311', 'id': '20210825171151933828', 'name': '法第三方士大夫的'}
        返回结果：charset='utf8',db='13123',engine='mysql',host='12311',name='法第三方士大夫的'
        :param parameter: 这是一个字典
        :return: 返回字典拼接后的字符
        """
        put_obj = ''
        # print(parameter)
        for key, value in parameter.items():
            if value == True:
                value = 1
            if value == False:
                value = 0
            if value is None:
                value = ''
            vaule_r = str(value) if type(value) == int else "'" + value + "'"
            put_obj  = put_obj + key + '=' +vaule_r +','
        return put_obj[:-1]
